How to Upgrade MySQL from 5.5 to 5.7

1. The short version of the installation is simple: update your package index, install the mysql-server package, and then run the included security and database initialization scripts.

MySQL 5.7: 3x Faster
https://www.mysql.com/products/enterprise/database/

The latest version for Ubuntu 14.04 is 5.5 but you can always update to a later version later. However, this is how I updated to 5.7:

note: I first had to removed the old mysql installation. This should not apply on the first installlation:

sudo apt-get remove mysql-server

sudo apt-get autoremove

Install mysql 5.7:

$ sudo apt-get update

$ sudo wget http://dev.mysql.com/get/mysql-apt-config_0.6.0-1_all.deb

$ sudo wget http://dev.mysql.com/get/mysql-apt-config_0.7.3-1_all.deb

$ sudo dpkg -i mysql-apt-config_0.7.3-1_all.deb

You'll see a prompt that asks you which MySQL product you want to configure. The MySQL Server option, which is highlighted, should say mysql-5.7. If it doesn't, press ENTER, then scroll down to mysql-5.7 using the arrow keys, and press ENTER again.

Once the option says mysql-5.7, scroll down on the main menu to Apply and press ENTER again. Now, update your package index.

NOTE: I have had problems where the prompts would not let me proceed with the installation when I selected mysql 5.7. One solution is that when you select ‘OK' on the first screen to exit and you return to the command line, just continue with the code below. This will install 5.7. It may be because the previous mysql install was not completely eliminated, thus keeping the same root password which is asked in the later steps below.

Then:

$ sudo apt-get update

$ sudo apt-get install mysql-server

Then:

$ sudo mysql_secure_installation

You'll be prompted to create a root password during the installation. Choose a secure one and make sure you remember it, because you'll need it later.

To check the mysql version:

$ mysql --version

Prerequisites: https://www.digitalocean.com/community/tutorials/initial-server-setup-with-ubuntu-14, also located at DigitalOceanAccountInfo

Testing MySQL:

sudo service mysql status

You'll see the following output (with a different PID):

mysql start/running, process 2689

If MySQL isn't running, you can start it with:

sudo service mysql start

For an additional check, you can try connecting to the database using the mysqladmin tool, which is a client that lets you run administrative commands. For example, this command says to connect to MySQL as root (-u root), prompt for a password (-p), and return the version.

mysqladmin -p -u root version

You should see output similar to this:

* MySQL Community Server 5.7.14 is running
vagrant@data-science-toolbox:~$ mysqladmin -p -u root version
Enter password:
mysqladmin  Ver 8.42 Distrib 5.7.14, for Linux on x86_64
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          5.7.14
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 16 min 43 sec

Threads: 1  Questions: 10  Slow queries: 0  Opens: 40  Flush tables: 1  Open tables: 33  Queries per second avg: 0.009

note: if you see any errors when logging in to phpmyadmin and clicking the ‘Databases' menu like:

#1146 - Table 'data_dictionary.CHARACTER_SETS' doesn't exist

in ssh:

sudo mysql_upgrade -u root -p --force

sudo service mysql restart

solution: http://stackoverflow.com/questions/19121383/mysql-issue-table-data-dictionary-character-sets-doesnt-exist, however you may still get the error in your browser. When I changed browsers (to Chrome) and logged in, the error was gone. Just Ctrl+f5 to reload the page.

Actually I have managed to resolve the issue. It is quite a strange one. It seems that Google Chrome [Firefox] default language is causing the issue since phpMyAdmin takes that language and translates itself to it. I have changed the language to English and logged out from my google account and the issue was magically resolved.

2. Tune MySQL

MySQL Tuner is a Perl script that connects to a running instance of MySQL and provides configuration recommendations based on workload. Ideally, the MySQL instance should have been operating for at least 24 hours before running the tuner. The longer the instance has been running, the better advice MySQL Tuner will give.

Install MySQL Tuner from Ubuntu’s repositories:

sudo apt-get install mysqltuner

To run it:

mysqltuner

You will be asked for the MySQL root user’s name and password. The output will show two areas of interest: General recommendations and Variables to adjust.

MySQL Tuner is an excellent starting point to optimize a MySQL server, but it would be prudent to perform additional research for configurations tailored to the application(s) utilizing MySQL on your Linode.

Securely Administer MySQL with an SSH Tunnel

https://www.linode.com/docs/databases/mysql/securely-administer-mysql-with-an-ssh-tunnel

MySQL vs MariaDB

Ken Favors . com

How to Upgrade MySQL from 5.5 to 5.7

Leave a Reply Cancel reply